[MY-SQL] 중요팁
1) 숫자 함수
ABS(X) : X 에 해당하는 절대 값을 돌려준다.
SIGN(X) : X의 값의 부호 값을 돌려 준다.(-1 : 음수, 0 : 0, 1 : 양수)
MOD(N,M) : N을 M으로 나눈 값의 나머지를 돌려 준다.
FLOOR(X) : 실수 X 값의 소수점 이하의 값은 버림을 한 정수 값을 돌려 준다.
CEILING(X) : 실수 X 값의 소수점 이하의 값을 올림을 한 정수 값을 돌려 준다.
ROUND(X,D) : 실수 X 값에서 소수점에서 D + 1번째에 해당하는 값을 올림한 값을 돌려 준다.
POWER(X,Y) : X의 값을 Y번 곱한 값을 돌려 준다.
RAND() : 0에서 1 사이의 숫자를 랜덤하게 발생시겨 돌려준다.
SELECT abs(-2),abs(2),sign(-45),sign(0),sign(34),mod(23,7);
SELECT floor(1,234),ceiling(1,234),round(2,49),round(2,51);
SELECT power(3,2),power(3,3),rand();
문자열함수
2) 문자열 함수
CHAR(N,...) : N(1-256의 정소)값의 아스키코드를 해당 문자로 출력한다.
CONCAT(str1,str2,...) : 각각의 str1,str2,...등을 하나의 스트링으로 돌려 준다. 단, NULL 이 있으면 NULL 을 출력한다.
CONCAT_WS(separator, str1, str2,...) : 각각의 str1,str2,...등을 separator을 구분자로 사용하여 하나의 스트링으로 출력한다.
LENGTH(str) : str의 문자의 길이를 출력한다.
SELECT char(48),char(76),concat(('My','S','ql'),concat('My',NULL,'Sq;');
SELECT concat_ws(',','My',NULL,'Sql',' ','is',' ','Good');
SELECT length('mysql'),locate('bar','foobarbar');
[그림2] 문자 함수를 실행한 결과
LOCATE(substr,str) : str에서 substr이 위치한 첫번째 위치를 정수 값으로 출력한다.
LEFT(str,len) : str에서 지정해준 자릿수 len만큼 왼쪽에서부터 문자열을 출력한다.
RIGHT(str,len) : str에서 지정해준 자릿수 len만큼 오른쪽에서부터 문자열을 출력한다.
SUBSTRING(str,pos,len) : 문자열 str에서 특정 시작위치 pos에서 지정된 길이 len만큼 문자를 출력한다.
LTRIM(str),RTRIM(str),TRIM(str) : str의 왼쪽,오른쪽,혹은 양쪽 모두의 공백 문자를 제거해 준다.
SELECT left('mysql is good',3),right('mysql is good',3);
SELECT substring('mysql is good',1,5);
SELECT ltrim(' mysql'),rtrim(mysql '),trim(' mysql ');
[그림3] 문자 함수를 실행한 결과
SPACE(N) : N개 만큼의 공백 문자를 만들어 출력한다.
REPLACE(str,from_str,to_str) : 문자열 str에서 특정문자 from_str을 특정 문자 to_str로 바꾸어 출력한다.
REPEAT(str,count) : 문자열 str을 count 갯수만큼 반복하여 반복한 값을 출력한다.
REVERSE(str) : 문자열 str을 역으로 출력 한다.
INSERT(str,pos,len,newstr) : 문자열 str에서 특정 위치 pos에서 특정 길이 len 만큼을 newstr문자열로 바꾸어 출력한다.
SELECT concat('a',space(6),'a');
SELECT replace('mysql is good','is','very'),repeat('a',3);
SELECT reverse('mysql'),insert('Quadratic',3,4,'What');
[그림4] 문자 함수를 실행한 결과
LCASE(str) : 문자열 str을 소문자로 바꾸어 출력한다.
UCASE(str) : 문자열 str을 대문자로 바꾸어 출력한다.
SELECT lcase('MYSQL'),ucase('mysql);
날짜시간함수
3) 날짜, 시간 함수
DAYOFWEEK(date) : 해당날짜의 요일값을 출력한다.(1=일요일,2=월요일,3=화요일,...,7=토요일)
WEEKDAY(date) : DayOfWeek() 함수와 같지만 요일값이 다르다.(0=월요일,1=화요일,...,6=일요일)
DAYOFMONTH(date) : 해당날짜의 날(1-31) 값을 출력한다.
DAYOFYEAR(date) : 일년중 몇번째 날(1-366)인지를 출력한다.
MONTH(date) : 해당 날짜의 달(1-12) 값을 출력한다.
DAYNAME(date) : 해당 날짜의 요일 값을 스트링으로 출력한다.
SELECT dayofweek('2003-02-22'),weekday('2003-02-23');
SELECT dayofmonth('2003-02-22'),dayofyear('2003-02-23');
SELECT month('2003-02-22),dayname('2003-02-23');
[그림6] 날짜, 시간 함수를 실행한 결과
MONTHNAME(date) : 해당 날짜의 달 값을 스트링으로 출력한다.
QUARTER(date) : 총 4분기중 해당 날짜의 분기 수 값을 출력한다.
WEEK(date,first) : 해당날짜가 1년중 몇번째 주인지를 출력한다.
YEAR(date) : 해당 날짜의 년도 값을 출력한다.
YEARWEEK(date,first) : 해당 날짜의 년도와 1년중 몇번째 주인지를 출력한다.
HOUR(time) : 해당 시간의 시 값을 출력한다.
SELECT monthname('2003-02-22'),quarter('2003-02-23');
SELECT week('2003-02-22'),year('2003-02-23');
SELECT yearweek('2003-02-22'),hour('10:22:32');
[그림7] 날짜, 시간 함수를 실행한 결과
MINUTE(time) : 해당 시간의 분 값을 출력한다.
SECOND(time) : 해당 시간의 초 값을 출력한다.
PERIOD_ADD(P,N) : P(YYMM or YYYYMM)값에서 N 달을 뺀 값을 출력한다.
PERIOD_DIFF(P1,P2) : P1(YYMM or YYYYMM)과 P2(YYMM or YYYYMM)의 달 차이를 출력한다.
TO_DAYS(date) : 0년 부터 date까지의 날 수를 출력한다.
FROM_DAYS(N) : N(날)에 해당하는 날짜를 출력한다.
SELECT minute('10:22:32'),second('10:22:32');
SELECT period_add(200302,3),period_diff(0302,200209);
SELECT to_days(20030222),from_days(731633);
[그림8] 날짜, 시간 함수를 실행한 결과
DATE_FORMAT(date,format) : date의 날짜 값을 원하는 format 값으로 바꾸어 출력한다.
TIME_FORMAT(time,format) : time의 시간 값을 원하는 format 값으로 바꾸어 출력한다.
CURDATE() : 현재의 날짜 값을 출력한다.
CURTIME() : 현재 시간을 출력한다.
NOW() : 현재 시스템의 날짜와 시간을 출력한다.
SELECT date_format('2003-02-22 22:23:00','%D %y %a %d %m %b %j');
SELECT time_format('22:23:00','%H %k %h %i %I %r %p %s %S');
SELECT curdate(),curtime(),now();
제어함수
4) 제어 함수
1) ~ 3)까지의 함수들은 이름만 들어도 대강은 어떤 함수일 거란 것을 예상 할 것이다. 그럼 제어 함수란 무엇인가.
이들 함수는 일반적인 프로그래밍 언어에서의 조건문과 같은 역할을 하는 함수들이다.
종류로는 IsNULL,NULLIF,IF,CASE등이 있으며 이들 함수로 인해 SQL문 안에서 값들을 제어 할 수가 있다.
IFNULL(expr1,expr2) : expr1 값이 널이면 expr2값이 출력이 되는 함수
NULLIF(expr1,expr2) : expr1 값과 expr2값이 같으면 NULL이 출력이 되고 다르면 expr1 값이 출력되는 함수
IF(expr1,expr2,expr3) : expr1 값이 참이면 expr2 값이 출력되고 거짓이면 expr3값이 출력되는 함수
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END :
value 값이 WHEN 다음의 compare-value값과 같으면 THEN 다음의 result 값을 출력하는 함수
SELECT IFNULL(NULL,2),IFNULL(1,2),IF(1 > 0,1,0),IF(1 < 0,1,0);
SELECT CASE 1 WHEN 1 THEN "one"
WHEN 2 THEN "two" ELSE "more" END;
[그림10] 제어 함수를 실행한 결과
지금까지 SQL문에서 많은 쓰이는 함수들에 대해서 공부해 보았다. 이들 함수 말고도 많은 다른 함수들이 존재하니
꼭 한번쯤은 봐 두길 바란다. 다음은 Gruop By 절과 GROUP BY 절과 같이 쓰이는 그룹함수에 대해서 알아보자.
GROUP BY
GROUP BY에 대해서
GROUP BY 문은 SELECT 문으로 데이터베이스로부터 얻어오는 내용 중에 최대값, 최소값, 합계, 평균 혹은 분산등
여러 열의 컬럼을 포함하는 집합 연산(aggregate function)이 적용된 경우에 주로 사용된다. 이런 연산에 사용하는
함수는 좀더 후에 배우고 먼저 GROUP BY문에 대해서 알아 보겠다.
GROUP BY절은 특정 컬럼의 값들을 하나로 묶는 역할을 한다. 이 말은 특정 컬럼의 값이 같으면 그 열 값은 하나의
열 값으로 취급한다는 말이다. 백문이 불여일견이라고 먼저 직접 테스트를 해보자. 테스트를 하기 위해서 다음과
같이 테이블을 하나 만든다.
CREATE TABLE group_test(
id varchar(10) not null,
number int not null,
string varchar(10) not null
}
위와 같이 만들었다면 테이블에 값을 다음 그림과 같이 넣어 보자.
SELECT * FROM group_test;
[그림11] Group_test 테이블 열(row)리스트
이제 말로만 떠들지 말고 직접 테스트를 해보자. 과연 어떤 결과가 나올지. string 컬럼을 그룹화하여 보자.
SELECT * FROM group_test GROUP BY string;
[그림12] String을 그룹화한 결과
위의 그림처럼 많은 변화가 생겼다. 이렇듯 특정 컬럼을 그룹화하면 그 컬럼의 동일한 값은 하나로 묶여 출력이 된다.
이제는 이 Group By 절을 사용하여 그룹함수를 사용해 보자.
그룹함수(aggregate function)에 대해서
그룹함수(aggregate function)에 대해서
그룹 함수도 여러개가 존재 하지만 여기서는 자주 사용하는 함수 몇 개에 대해서 설명을 하겠다.
그 전에 그룹함수라 하여 꼭 GROUP BY절과 같이 사용해야 하는 것은 아니며 단지 일반적으로
SELECT 컬럼1,그룹함수 FROM test1
와 같은 비슷한 형식일 경우에는 GROUP BY절을 같이 사용하여야 한다. 그렇지 않고 그룹함수만 쓸 경우에는 GROUP BY 절을
꼭 같이 써야 할 필요는 없다.
AVG() : 이 함수는 ()안의 컬럼들의 평균을 출력하는 함수이다.
MAX() : 이 함수는 ()안의 컬럼들 중 가장 큰 값을 출력하는 함수이다.
MIN() : 이 함수는 ()안의 컬럼들 중 가장 작은 값을 출력하는 함수이다.
SUM() : 이 함수는 ()안의 컬럼들의 합계을 출력하는 함수이다.
COUNT() : 이 함수는 레크드의 수를 출력하는 함수이다.
SELECT count(*),avg(number),max(string),min(id),sum(number) FROM group_test;
[그림13] 그룹 함수를 실행한 결과
이제 이들을 활용해 보자. 어떤 경우에 이들을 효율적으로 사용할 수 있을까?
예를 들어 어떤 사람이 'id가 'kim'이라는 사람의 number의 값의 평균과 총점을 구하고 싶다'라고 주문을 해왔다.
그러면 여러분들은 어떻게 하겠는가? 지금까지 배운데로 먼저 이들의 값을 구해보자.
SELECT avg(number),sum(number) FROM group_test WHERE id = 'kim';
[그림14] id가 'kim'인 사람의 평균과 총점 결과
여기까지는 문제가 없다. 그렇다면 이제는 id별로 그 사람들의 number의 값의 평균과 총점을 구하고 싶다고 한다.
그러면 여러분들은 어떻게 하면 되겠는가? 그렇다. 이럴때 유용하게 쓰이는 것이 GROUP BY 절이다.
이를 사용하여 id를 그룹화하면 이들 각각의 평균과 총점을 구할 수 있다.
SELECT id,avg(number),sum(number) FROM group_test GROUP BY id;
[그림15] 각각의 사람의 평균과 총점 결과
ORDER BY 절에 대해서 이제는 결과 값을 정렬하는 방법에 대해서 알아보자. 정렬을 하기 위해서 사용하는 것이 ORDER BY 절이다.
이 절은 특정 컬럼을 중심으로 내림차순 정렬 혹은 오름차순 정렬을 할 수가 있다. 그럼 먼저 형식을 보자.
ORDER BY 특정 컬럼1 [Asc|Desc],특정 컬럼2 [Asc|Desc],...
이며 제일 먼저 특정 컬럼1이 정렬이 되고 그 정렬 속에서 특정 컬럼2가 정렬이 된다. 그럼 직접 실습을 해보자.
SELECT * FROM group_test ORDER BY id ASC,number DESC, string ASC;
[그림16] ORDER BY 절을 사용한 결과
이렇게 ORDER BY 절을 이용하면 원하는 정렬이 가능하다.
여기서 한가지 주의할 점이 있는데 ORDER BY 절을 먼저 쓰고 GROUP BY절을 나중에 쓰면 오류가 발생하므로
반드시 GROUP BY와 ORDER BY 절을 같이 쓸 경우에는 GROUP BY절을 먼저 쓰고 뒤에 order by절을 쓰길 바란다.
일반적인 SQL문 순서
SELECT 컬럼1,컬럼2,... FROM 테이블1,테이브2,.. WHERE 조건문 GROUP BY 그룹화 할 컬럼1,컬럼2,...
ORDER BY 정렬할 컬럼1 [ASC|DESC],컬럼2,[ASC|DESC],...
서브쿼리(SubQuery?)에 대해서
서브쿼리(SubQuery)에 대해서
드디여 여기까지 왔다. 이 부분은 조인(Join)처럼 매우 중요하므로 꼭 마스터 하길 바란다.
이젠 조인(Join)에 대해서 어느 정도 이해가 가는가? 그렇다면 조인(join)만큼이나 중요하고
복잡한 서브쿼리(SubQuery)에 대해서 알아보자. 먼저 알아 보기 전에 다음과 같이 테이블을 만든다.
참고로 MySQL에서는 서브쿼리(SubQuery)를 지원하지 않는다. mysql.com 에 따르면 4.1버전부터 지원을 한다고
하며 우리는 일단 PostgreSQL를 사용하여 테스트를 할 것이다.
CREATE TABLE Student(
ID varchar(50),
Name varchar(50),
Class int
);
CREATE TABLE Score(
ID varchar(50),
Score int
);
위와 같이 테이블을 만들었다면 [그림17]과 [그림18]처럼 열를 추가한다.
SELECT * FROM Student;
[그림17] Student 테이블 속 열
SELECT * FROM Score;
[그림16] Score 테이블 속 열
추가 하였다면 이제 본격적으로 서브쿼리(SubQuery)에 대하여 공부하여 보자.
서브쿼리(SubQuery)는 SELECT, INSERT, UPDATE, DELETE 문이나 다른 서브쿼리(SubQuery) 내부에 중첩된 SELECT 쿼리이다.
서브쿼리(SubQuery)는 식이 허용되는 모든 위치에서 사용할 수 있고 ()로 묶어서 사용된다.
다음 예제에서 서브쿼리(SubQuery)는 SELECT 문에서 Score 라는 컬럼으로 사용된다.
SELECT a.Name, a.Class,
(SELECT Score FROM Math AS b
WHERE b.ID = a.ID) AS Score
FROM Student AS a
이것은 학생들의 이름과 반, 점수을 뽑아내는 서브쿼리(SubQuery)문이다.
대부분의 서브쿼리(SubQuery)문은 조인(join)으로 나타낼 수 가 있다.
또한 서브쿼리(SubQuery)가 포함된 문장이나 의미상 동일한 문장에서는 서브쿼리(SubQuery)나 조인(Join)이나 성능면에서는 차이가 없다.
이제 위의 문장을 테스트 해보자.
[그림19] 위의 서브쿼리(SubQuery)문 실행 결과
위에서 하위 쿼리(SubQuery)는 대부분이 조인(Join)으로 출력할 수 있다고 했다. 그럼 이젠 앞 강에서 배운 조인(Join)으로 출력해보자.
SELECT a.Name,a.Class,b.Score FROM Student a INNER JOIN Score b ON b.ID = a.ID;
[그림20] 위의 서브쿼리(SubQuery)문을 조인(join)문으로 변경하여 실행한 결과
내용을 보면 똑같은 결과를 출력하였다. 서브쿼리(SubQuery)에 대해서 위에서 설명할 때 식이 허용하는한 어디에서든
사용할 수 있다고 했다. 그럼 이제는 서브쿼리(SubQuery)문을 현재의 위치가 아닌 다른 위치에서 사용하여 보자.
기본적인 형식은 다음과 같다.
SELECT 컬럼1,컬럼2,.....
FROM 테이블1 where 비교할 컬럼 [IN|NOT IN]
(select 비교할 컬럼 From 테이블2)
여기서 주목할 곳은 IN과 NOT IN이다. IN은 =와 같은 뜻이며 NOT IN은 !=과 같은 뜻으로 사용된다. 그럼 간단한 실습을 해보자.
SELECT ID,Name,Class FROM Student WHERE ID IN (SELECT ID FROM Score);
SELECT ID,Name,Class FROM Student WHERE ID NOT IN (SELECT ID FROM Score);
[그림21] 위의 서브쿼리(SubQuery)문에서 IN과 NOT IN의 차이
이상과 같이 서브쿼리(SubQuery)에 대해서 살펴보았다. 앞으로 데이터 베이스를 사용하면서 서브쿼리(SubQuery)문과
조인(join)문은 성능향상을 위해서도 꼭 필요한 것들이므로 반드시 마스터하길 바란다. 참고로 MSSQL과 오라클등
상업적인 데이터 베이스는 IN,NOT IN뿐만이 아니라 =,!=,<,>등을 서브쿼리(SubQuery)에서도 지원하고 있다.
이건 오픈 소스 데이터 베이스의 한계로 앞으로 이들 오픈 소스 데이터 베이스도 역시 지원을 할 것이다.
[오라클] 기본팁
<< 오라클 >>
** 오라클 사용시 "commit;" 명령어 사용함 **
1. [테이블의 필드 확인] - desc 테이블명;
-예) desc cybedu_board01;
2. [날짜 표현] - sysdate (MS-SQL에서는 getdate())
-예) select sysdate from cybedu_section;
3. [중간 글 틀렸을때 수정] - c옵션은 변환, r옵션은 변환값으로 재실c행
예) SQL> 2
2* where substring(convert(varchar2(20),sysdate,120),1,10) >= 2004-07-29
SQL> c/varchar2/varchar
2* where substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-29
SQL> 3
3* and substring(convert(varchar2(20),sysdate,120),1,10) <= 2004-08-05
SQL> c/char2/char
3* and substring(convert(varchar(20),sysdate,120),1,10) <= 2004-08-05
SQL> r
1 select popWidth, popHeight, popFlag from cybedu_popup
2 where substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-29
3 and substring(convert(varchar(20),sysdate,120),1,10) <= 2004-08-05
4* order by 2004-07-29 desc, 2004-08-05 desc, popsn desc
where substring(convert(varchar(20),sysdate,120),1,10) >= 2004-07-29
=> 오라클에서는 substring -> substr, convert사용안함, varchar -> to_char로 사용
* 아래는 문제 해결 구문 *
sql = "select popWidth, popHeight, popFlag from cybedu_popup";
sql = sql + " where substr(to_char(sysdate,'YYYY-MM-DD'),1,10) >= popstartday";
sql = sql + " and substr(to_char(sysdate,'YYYY-MM-DD'),1,10) <= popendday";
sql = sql + " order by popstartday desc, popendday desc, popsn desc";
4. oracle에서 left outer join을 하시려면
[MY-SQL]
select aa.day,aa.count,bb.apply
from (select day,count from tblCount where year='2004' and month='06') aa left outer join
(select day, count(*) as apply from tblApply
where year='2004' and month='06' and Deliverystate='신청접수' group by month,day)
bb on aa.day = bb.day;
[오라클]
select aa.day,aa.count,bb.apply
from (select day,count from tblCount where year='2004' and month='06') aa ,
(select day, count(*) as apply from tblApply
where year='2004' and month='06' and Deliverystate='신청접수' group by month,day)
bb where aa.day = bb.day(+) <--이런식으로 써야 outer join이 걸립니다...
5. 오라클에서 컬럼 자동 증가 하기
create table cybedu_message (
msgid integer not null, /* 일련번호*/
msgSendId varchar2 (10), /*쪽지보낸사람 ID*/
~ ~ ~
msgDate date, /* 쪽지보낸 날짜*/
~ ~ ~
msgFlagR varchar2 (2), /* 받은쪽지함에 보여지는 여부*/
primary key ( msgid ) ) ;
create sequence msgid_seq; ==> 자동증가 (ms-sql: identity(1,1))
6. **** 오라클 기본 문법 ****
삭제: delete from 테이블 이름 where 조건절 ;
[MS-SQL] 기본 사용법
1. CEILING -> 소숫점 아래수를 반올림해서 정수값 돌려줌
SELECT Count(*),CEILING(CAST(Count(*) AS FLOAT)/보여줄 페이지수)
FROM 테이블명
WHERE search_word LIKE '%SearchString%'
rs.Open strSQL, dbcon
intTotalCount = rs(0) ' 총 페이지 수 (검색어가 있을때, 없을때)
intTotalPage = rs(1) ' 보여줄 페이지 수의 값(intPageSize가 10이므로 10페이지씩 몇개를 보여줄지의 수)
rs.Close
2. Top 숫자 -> 상위의 "숫자"만큼 뿌려줌
strSQL = "SELECT Top " & intNowPage * intPageSize & " brdnum, brdtitle, brdname, writedatetime, brdcount "
strSQL = strSQL+ "FROM gong_news_board WHERE brdstate = '02' "
if search_word <> "" then
strSQL = strSQL & " AND " & search_word & " LIKE '%" & SearchString & "%'"
end if
strSQL = strSQL & " ORDER BY brdnum DESC"
3. ISNULL 사용법
예> SELECT EMPNO, ENAME, DEPTNO FROM EMP;
EMPNO ENAME DEPTNO SALARY
--------------------------------------------
01001 김갑돌 101 NULL
01002 이몽룡 102 10000
01003 홍길동 NULL NULL
01004 고인돌 NULL 5000
01005 손오공 111 NULL
이러한 경우,
SELECT EMPNO, ENAME, ISNULL(DEPTNO,'999'), ISNULL(SALARY,0)
FROM EMP;
EMPNO ENAME DEPTNO SALARY
--------------------------------------------
01001 김갑돌 101 0
01002 이몽룡 102 10000
01003 홍길동 999 0
01004 고인돌 999 5000
01005 손오공 111 0
이와 같이 얻으실수 있습니다.
4. sql문 case -> if 문으로 변환
[case 문] [if 문]
select m.[번호] select m.[번호]
, m.[이름] , m.[이름]
, [회원구분] = , [회원구분] =
case if (
when m.[번호] in ( m.[번호] in (
select [번호] select [번호]
from [정회원] as r ---> from [정회원] as r
where r.[번호] = m.[번호] where r.[번호] = m.[번호]
) )
then '정' , '정'
else '준' , '준')
end
, m.[전화번호] , m.[전화번호]
from [회원] as m from [회원] as m
order by m.[번호] order by m.[번호]
5. ** 해당 날짜 사이의 값 가져오기 **
select count(distinct lecNo) from cybedu_curriculum, cybedu_course, cybedu_section,
cybedu_lecture left join cybedu_train on ( lecNo = traNo )
where convert(varchar(10),getdate(),120) between secStartregist and secEndregist
and secNo=lecSecno and crsCode=lecCrscode
and curCode = crsCurcode and curCode = 'curCode' and secFlag='0'
=> 설명 : from 뒤의 테이블과 join 뒤의 테이블을 left join해서(lecNo = traNo) 값중에서
between뒤의 시작날짜와 종료날짜 사이의 값을 가져오라는것..
(아래 sql문은 업그레이드 한 것인거 같네여~)..위랑 같은 내용
select count(distinct lecNo) from cybedu_curriculum, cybedu_course, cybedu_section,
cybedu_lecture left join cybedu_train on ( lecNo = traNo ) where convert(varchar(10),getdate(),120) between secStartregist and secEndregist
and secNo=lecSecno and crsCode=lecCrscode
and curCode = crsCurcode and secFlag='secFlag' and curcode <> 'offline'
6. delete 구문
delete 테이블명 where 조건문
7. update 구문
update 테이블명 set 바꿀구문 where 조건구문
[MS-SQL] 중요팁2
----------------------------------<< MS-SQL 고급 사용법 >>-----------------------------------
1. 기본 쿼리
sp_spaceused : DB사용자 정보 보여줌
sp_dboption : 가능한 DB 옵션들
sp_helpdb : 각DB이름,사이즈,owner등을 보여줌
sp_helpdb 디비명 : 디비의 설명
2. 다른 DB의 테이블 불러서 사용하기
- 우선 sql서버 엔터프라이즈 관리자에서/ENT01/users/ 사용자를 추가한다.
- DB -> ENT01, 111
- 111에서 ENT01의 board14테이블 사용하려면
ex) select * from ENT01.111.board14 -> select * from 해당DB.테이블소유자명.테이블명
[MS-SQL] 중요팁
1. 오픈 쿼리 사용법
* 링크드 리스트가 설정이 되어 있어야 오픈쿼리 사용됨.
(MS-SQL에서 사용됨)
------------------------------------ (사용예) ---------------------------------------
/*
sql = "select convert(varchar(20),user_id), convert(varchar(20),name), convert(varchar(20),jumin)";
sql = sql + " from OPENQUERY(jejunet,'SELECT user_id, jumin, name FROM member_table')
as member_table where (convert(varchar(20),name) = ?) and (substring(convert(varchar(20),jumin),1,6) = ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,pass);
rs = pstmt.executeQuery();
if(rs.next())
{
student.setId(rs.getString(1));
*/
sql = "select korname, usrjumin from cybedu_userinfo where (korname = ?) and (usrjumin = ?)";
//out.print(sql);
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,pass);
rs = pstmt.executeQuery();
if(rs.next())
{
student.setId(rs.getString(1));
--------------------------------------------------------------------------------------
2. 병렬 처리 사용 방법
* 생성시 같이 정의
create table line_item (item_no number(7),
item_name varchar(20), ... ) parallel 20;
create index item_index on line_item (item) parallel 20;
* 이미 작성된 오브젝트에 대한 병렬처리
alter table line_item parallel 20;
alter index item_index rebuild parallel 20;
응용프로그램의 수정없이 즉시 사용
* 해당 SQL만 병렬로 처리 : Hint 기능 사용
select /*+ parallel(line_item,20) */ item_name, ...
from line_item, products .....
각종 DBMS JDBC 드라이버 셋팅법 정리
//********** 각 경우별 드라이버 연동하기 **************/
** JDK jdbc-odbc driver (Type1) **
연결 URL : "jdbc:odbc:"
드라이버 클래스 : sun.jdbc.odbc.JdbcOdbcDriver
** Oracle thin driver (Type4) **
연결 URL : "jdbc:oracle:thin:@:port:"
드라이버 클래스 : oracle.jdbc.driver.OracleDriver
** Oracle oci driver (Type2) **
연결 URL : "jdbc:oracle:oci:@"
드라이버 클래스 : oracle.jdbc.driver.OracleDriver
** Sybase jConnect driver (Type2) **
연결 URL : "jdbc:sybase:Tds::"
드라이버 클래스 : com.sybase.jdbc2.jdbc.SybDriver
** Informix JC1 driver (Type4) **
연결 URL : "jdbc:informix-sql://:/:INFORMIXSERVER"
드라이버 클래스 : com.informix.jdbc.IfxDriver
** mSQL Imaginary JDBC driver (Type4) **
연결 URL : "jdbc:msql://:/"
드라이버 클래스 : com.imaginary.sql.msql.MsqlDriver
** Postgres driver (Type4) **
연결 URL : "jdbc:postgresql://:/"
드라이버 클래스 : postgresql.driver
** MM MySQL driver (Type4) **
연결 URL : "jdbc:mysql://:/"
드라이버 클래스 : org.git.mm.mysql.Driver
mysql은 JConnector 3.0 부터는 com.mysql.jdbc.Driver
연결 URL 은 jdbc:mysql://localhost/dbname?Unicode=true&characterEncoding=EUC_KR
같이 인코딩 타입을 직접 줌으로써 한글 변환문제에 좀더 쉽게 해결할 수 있음.
// ******************************Connected To IBM AS/400
Class.forName("com.ibm.as400.access.AS400JDBCDriver");
com = Driver.Manager.getConnection("jdbc:as400://10.20.30.40/testlib;user=user;password=pass");
// ******************************Connected To Unisql
Class.forName("unisql.jdbc.driver.UniSQLDriver");
con = Driver.Manager.getConnection("jdbc:unisql:10.20.30.40:43300:demodb:::", "user","pass");
// ******************************Connected To Jdbc-Odbc Type - 1 Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// con = DriverManager.getConnection("Jdbc:Odbc:dsnname","userid","password");
con = DriverManager.getConnection("jdbc:odbc:Driver={SQL Server};Server=servername;Database=pubs","userid","password");
// ******************************Connected To Ms-Access JDBC ODBC Driver .
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("Jdbc:Odbc:dsnname","","");
// con = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=G:/admin.mdb","","");
// ******************************Connected To Ms-Access Type-3 Driver.
Class.forName ("acs.jdbc.Driver");
String url = "jdbc:atinav:servername:5000:C:\\admin.mdb";
String username="Admin";
String password="";
Connection con = DriverManager.getConnection(url,username,password);
// ******************************Connected To Microsoft SQL.
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://servername:1433","userid","password");
// ******************************Connected To Merant.
Class.forName("com.merant.datadirect.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:merant:sqlserver://servername:1433;User=userid;Password=password");
// ******************************Connected To Atinav SqlServer.
Class.forName ("net.avenir.jdbc2.Driver");
con= DriverManager.getConnection("jdbc:AvenirDriver://servername:1433/pubs","userid","password");
// ******************************Connected To J-Turbo.
String server="servername";
String database="pubs";
String user="userid";
String password="password";
Class.forName("com.ashna.jturbo.driver.Driver");
con= DriverManager.getConnection("jdbc:JTurbo://"+server+"/"+database,user,password);
// ******************************Connected To jk Jdbc Driver.
String url= "jdbc:jk:server@pubs:1433";
Properties prop = new Properties();
prop.put("user","userid");//Set the user name
prop.put("password","password");//Set the password
Class.forName ("com.jk.jdbc.Driver").newInstance();
con = DriverManager.getConnection (url, prop);*/
// ******************************Connected To jNetDirect Type - 4 Driver
String sConnect = "jdbc:JSQLConnect://127.0.0.1/database=pubs&user=userid&password=password";
Class.forName ("com.jnetdirect.jsql.JSQLDriver").newInstance();
Connection con= DriverManager.getConnection(sConnect);
// ******************************Connected To AvenirDriver Type - 4 Driver
// String url= "jdbc: AvenirDriver: //servername:1433/pubs";
// java.util.Properties prop = new java.util.Properties ();
// prop.put("user","userid");
// prop.put("password","password");
Class.forName ("net.avenir.jdbc2.Driver");
System.out.println(" Connected To AvenirDriver Type - 4 Driver");
con= DriverManager.getConnection("jdbc:AvenirDriver://servername:1433/pubs","userid","password");
// ******************************Connected To iNet Sprinta2000 Type - 4 Driver
String url="jdbc:inetdae7:servername:1433";
String login="userid";
String password="password";
Class.forName("com.inet.tds.TdsDriver");
System.out.println(" Connected To iNet Sprinta2000 Type - 4 Driver");
con=DriverManager.getConnection(url,login,password);
// ******************************Connected To iNet Opta2000 Type - 4 Driver
String url="jdbc:inetdae7:servername:1433";
String login="sagar";
String password="sagar";
Class.forName("com.inet.tds.TdsDriver").newInstance();
System.out.println(" Connected To iNet Opta2000 Type - 4 Driver");
con=DriverManager.getConnection(url,login,password); |
